package ibm_package;

import java.io.IOException;
import java.io.PrintWriter;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;



import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.mysql.jdbc.Connection;

/**
 * Servlet implementation class DatabaseAccess
 */
public class DatabaseAccess extends HttpServlet {
	private static final long serialVersionUID = 1L;
	private static Connection conn;
	private static String driver = "com.mysql.jdbc.Driver";
	private static Statement stmt;
       
    /**
     * @see HttpServlet#HttpServlet()
     */
    public DatabaseAccess() {
        super();
        // TODO Auto-generated constructor stub
    }

	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		
		PrintWriter out = response.getWriter();
		out.println("<h1>Access the JDBC of IBM's Trainees</h1>");
		
		try
		{
			Class.forName(driver);
			conn = (Connection) DriverManager.getConnection("jdbc:mysql://localhost/ibm_training","root","");
			 // Execute SQL query
		      Statement  stmt = conn.createStatement();
		        String sql;
		        sql = "SELECT * FROM trainee";
		        ResultSet rs = stmt.executeQuery(sql);

		        // Extract data from result set
		        while(rs.next()){
		           //Retrieve by column name
		           int id  = rs.getInt("id");
		           String first = rs.getString("Fname");
		           String last = rs.getString("Lname");
		           String faculty = rs.getString("faculty");
		           String field = rs.getString("field");
		           String mentor = rs.getString("mentor");

		           //Display values
		           out.println("ID: " + id + "<br>");
		           out.println("Fname: " + first + "<br>");
		           out.println("Lname: " + last + "<br>");
		           out.println("faculty: " + faculty + "<br>");
		           out.println("field: " + field + "<br>");
		           out.println("mentor: " + mentor + "<br><br>");
		        }

			
		}catch(Exception e){
			e.printStackTrace();
		}
		
		
				
	 }

	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		// TODO Auto-generated method stub
	}

}
